package com.dao.impl;

import com.dao.ProductDao;
import com.mysql.cj.util.StringUtils;
import com.pojo.Classify;
import com.pojo.Product;
import com.pojo.ProductType;
import com.utils.JDBCUtil;

import java.util.List;

public class ProductDaoImpl implements ProductDao {

    /**
     * 分页查询所有
     * @param start
     * @param limit
     * @return
     */
    @Override
    public List<Product> findAll(int start,int limit) {
        return JDBCUtil.queryList(Product.class,"select * from s_product limit ?,?",start,limit);
    }

    /**
     * 根据id查询一条数据
     * @param id
     * @return
     */
    @Override
    public Product findByID(String id) {
        return JDBCUtil.queryOne(Product.class,"SELECT p.*,IFNULL(SUM(product_num),0) productNum FROM s_product p LEFT JOIN  s_order_product o ON p.`id` = o.`product_id` WHERE p.id = ? GROUP BY p.id",id);
    }

    /**
     * 获取最大行数
     * @return
     */
    @Override
    public int selectCount() {
        return JDBCUtil.queryCount("select count(*) from s_product");
    }

    /**
     * 根据ID删除一条数据
     * @param id
     * @return
     */
    @Override
    public int deleteP(String id) {
        return JDBCUtil.update("delete from s_product where id = ?",id);
    }

    /**
     * 添加
     * @param product
     * @return
     */
    @Override
    public int addP(Product product) {
        return JDBCUtil.update("insert into s_product values(?,?,?,?,?,?,?,?)",product.getId(),product.getProductName(),
                product.getProductImage(),product.getPrice(),
                product.getProductType(),product.getProductDesc(),
                product.getCreateTime(),product.getProductBrand());
    }

    /**
     * 修改
     * @param product
     * @return
     */
    @Override
    public int update(Product product) {
        return JDBCUtil.update("UPDATE s_product SET product_name = ?, product_image = ?, product_desc = ?, price = ?, product_type = ?, product_brand = ?WHERE id = ? "
        ,product.getProductName(), product.getProductImage(),product.getProductDesc(), product.getPrice(),product.getProductType(),product.getProductBrand(),product.getId()
        );
    }

    /**
     * 新品
     * @return
     */
    @Override
    public List<Product> newProduct() {
        return JDBCUtil.queryList(Product.class,"SELECT * FROM s_product ORDER BY create_time DESC limit 6");
    }


    /**
     * 分类查询
     * @param productTypeID
     * @return
     */
    @Override
    public List<Product> sortProduct(String productTypeID,int limit) {
        return JDBCUtil.queryList(Product.class,"SELECT * FROM s_product WHERE product_type = ? ORDER BY create_time DESC limit ?",productTypeID,limit);
    }


    /**
     * 商品查询
     * @param classify
     * @return
     */
    @Override
    public List<Product> selectProducts(Classify classify) {
        StringBuffer sql = new StringBuffer("select p.*,ifnull(sum(o.product_num),0) productNum from s_product p left join s_order_product o on p.id = o.product_id where 1=1");
        if (!StringUtils.isNullOrEmpty(classify.getTypeId())){
            sql.append(" and product_type = '"+classify.getTypeId()+"'");
        }
        if (classify.getBrands()!=null&&classify.getBrands().length>0){
            sql.append("and product_brand in(");
            for (String brand : classify.getBrands()) {
                sql.append("'"+brand+"',");
            }
            sql.deleteCharAt(sql.length()-1);
            sql.append(")");
        }
        if (!StringUtils.isNullOrEmpty(classify.getKeyWord())){
            sql.append(" and product_name like '%"+ classify.getKeyWord()+"%'");
        }
        if (classify.getMin()!=null&&classify.getMin()!=0){
            if (classify.getMax()!=0){
                sql.append(" and price between "+ classify.getMin()+" and "+ classify.getMax());
            }else{
                sql.append(" and price >= "+ classify.getMin());
            }
        }else{
            if (classify.getMax()!=null&&classify.getMax()!=0){
                sql.append(" and price <= "+ classify.getMin());
            }
        }
        sql.append(" group by p.id");
        if (classify.getOrderBy()!=null&&classify.getOrderBy().length()>0){
            sql.append(" order by "+classify.getOrderBy()+" desc");
        }
        System.out.println(sql);
        return JDBCUtil.queryList(Product.class,new String(sql));
    }

    /**
     * 根据商品id查询同类商品
     * @param id
     * @return
     */
    @Override
    public List<Product> selectSimilarGoodsByID(String id) {
        return JDBCUtil.queryList(Product.class,"SELECT p.*,IFNULL(SUM(product_num),0) productNum FROM s_product p LEFT JOIN s_order_product o ON p.`id` = o.product_id WHERE p.product_type = (SELECT product_type FROM s_product WHERE id = ?) GROUP BY p.id",id);
    }
}
